package com.lin.fengxiu.repository;

import com.lin.fengxiu.model.Coupon;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.Date;
import java.util.List;

/**
 * @ClassName CouponRepository
 * @Description TODO
 * @Author wushaopei
 * @Date 2021/3/24 22:15
 * @Version 1.0
 */
@Repository
public interface CouponRepository extends JpaRepository<Coupon,Long> ,JpaSpecificationExecutor{

//    @Query("select * from coupon \n" + "join coupon_category\n" + "on coupon.id = coupon_category.coupon_id\n" + "join category\n" + "on coupon_category.category_id = category.id\n" + "where category.id = 32 ")
    @Query("select c from Coupon c\n" + "join c.categoryList ca\n" + "join Activity a on a.id = c.activityId\n" + "where ca.id = :cid\n" + "and a.startTime < :now\n" + "and a.endTime > :now")
    List<Coupon> findByCategory(@Param("cid")Long cid, @Param("now")Date now);

    //全场券查询
    @Query("select c from Coupon c\n" + "join  Activity a on c.activityId = a.id\n" + "where c.wholeStore = :isWholeStore\n" + "and a.startTime < :now\n" + "and a.endTime > :now")
    List<Coupon> findByWholeStore(@Param("isWholeStore")boolean isWholeStore, @Param("now")Date now);

    /**
     * @Description TODO 查询未使用的优惠券
     * @param uid
     * @return
     */
    @Query("select c from Coupon c \n" + "join UserCoupon uc\n" + "on c.id = uc.couponId\n" + "join User u \n" + "on u.id = uc.userId\n" + "where uc.status = 1\n" + "and u.id = :uid\n" + "and c.startTime < :now\n" + "and c.endTime > :now\n" + "and uc.orderId is null")
    List<Coupon> findMyAvailable(@Param("uid") Long uid,@Param("now")Date now);

    /**
     * @Description TODO 查询已使用的优惠券
     * @param uid
     * @param now
     * @return
     */
    @Query("select c From Coupon c \n" + "join UserCoupon uc \n" + "on c.id = uc.couponId\n" + "join User u \n" + "on u.id = uc.userId\n" + "where u.id = :uid\n" + "and uc.status = 2 \n" + "and uc.orderId is not null\n" + "and c.startTime < :now\n" + "and c.endTime > :now")
    List<Coupon> findMyUsed(@Param("uid") Long uid,@Param("now")Date now);

    /**
     * @Description TODO 已过期优惠券
     * @param uid
     * @param now
     * @return
     */
    @Query("select c From Coupon c\n" + "join UserCoupon uc \n" + "on c.id = uc.couponId\n" + "join User u \n" + "on u.id = uc.userId\n" + "where u.id = :uid\n" + "and uc.orderId is null\n" + "and uc.status <> 2\n" + "and c.endTime < :now")
    List<Coupon> findMyExpired(@Param("uid") Long uid,@Param("now")Date now);
}
